# coding=utf-8
"""
作者：董新强 
创建时间：2020-02-16
描述：比较两个数据字段的变化
"""
import pymysql


def comapreDbs():
    conn_std = pymysql.connect(host='mysql2c4g.cedwy3bhqgon.rds.cn-north-1.amazonaws.com.cn', port=3306, user='onlyview', passwd='6*^Hihiyasy9%^&^896895$%&*$87', db='information_schema', charset='utf8')
    # conn_dev = pymysql.connect(host='127.0.0.1', port=3306, user='dev', passwd='dev123', db='information_schema', charset='utf8')
    conn_dev = pymysql.connect(host='dev.cedwy3bhqgon.rds.cn-north-1.amazonaws.com.cn', port=19890, user='dns-dev', passwd='##dv_$$&123??', db='information_schema', charset='utf8')
    std_tb_dic = get_tables(conn_std, 'db_cat')
    dev_tb_dic = get_tables(conn_dev, 'db_cat')

    has_diff = False
    print('############################################')
    for tb, f1 in dev_tb_dic.items():
        if tb not in std_tb_dic:
            print('【新增表】=>:', tb)
            continue
        f2 = std_tb_dic[tb]
        new_f = f1 - f2
        if new_f:
            has_diff = True
            print('【add', tb, '】=>', new_f)
        del_f = f2 - f1
        if del_f:
            has_diff = True
            print('【del', tb, '】=>', del_f)

    if has_diff:
        print('#                比较完毕,存在以上差异         ')
    else:
        print('#                没有差异                   #')

    print('############################################')


def get_tables(conn, db_name):
    table_dic = {}
    cur = conn.cursor()
    cur.execute("select TABLE_NAME, TABLE_COMMENT from TABLES WHERE TABLE_SCHEMA='%s' order by TABLE_NAME" % (db_name,))

    for v in cur:
        table_dic[v[0]] = set()
    cur.close()

    for tb in table_dic:
        cur = conn.cursor()
        sql = "select COLUMN_NAME,COLUMN_TYPE from columns  where table_schema='" + db_name + "' and TABLE_NAME ='" + tb + "'"
        cur.execute(sql)

        for v in cur:
            table_dic[tb].add(v)

        cur.close()
    return table_dic


if __name__ == '__main__':
    comapreDbs()
